package com.seeyon.apps.ebs.rc.dao.impl;

import com.alibaba.fastjson.JSONObject;
import com.seeyon.apps.ebs.rc.dao.RcDao;
import com.seeyon.cap4.form.api.FormApi4Cap4;
import com.seeyon.cap4.form.bean.FormBean;
import com.seeyon.cap4.form.bean.FormFieldBean;
import com.seeyon.ctp.common.AppContext;
import com.seeyon.ctp.organization.po.OrgMember;
import com.seeyon.ctp.util.DBAgent;
import com.seeyon.ctp.util.JDBCAgent;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class RcDaoImpl implements RcDao {
    private static final Log log = LogFactory.getLog(RcDaoImpl.class);


    @Override
    public List<Map<String, Object>> getAllPeopleEbsRc(JSONObject param) {
        List<Map<String, Object>> result = new ArrayList<>();
        JDBCAgent agent = null;
        try {
            String sql = "select state,count(id) as sl from ebs_rc_mx where org_department_id in (:depids) and dq_date=:dqDate group by state";
            Map<String, Object> dataMap = new HashMap<>();
            dataMap.put("depids", param.getJSONArray("depIds"));
            dataMap.put("dqDate",param.get("dqDate"));
            agent = new JDBCAgent(false, false);
            agent.executeNamedSql(sql,dataMap);
            result = agent.resultSetToList();
        }catch (Exception e){
            log.error("查询人员信息异常！！！", e);
        }finally {
            agent.close();
        }
        return result;
    }

    @Override
    public List<Map<String, Object>> getAllPeopleByName(Map<String, Object> param) {
        List<Map<String,Object>> result = new ArrayList<>();
        JDBCAgent agent = new JDBCAgent(false, false);
        try {
            String sql = "select c.id as id from addressbook  a join ctp_enum_item b on a.EXT_ATTR_31 = b.id join org_member c  "
                    +" on a.MEMBER_ID = c.id where b.showvalue = '是' and c.is_enable='1' and c.IS_DELETED='0' and c.STATUS='1' and c.is_admin='0'";
            Object depId = param.get("depId");
            if (depId != null && !"".equals(depId)){
                sql += " and c.org_department_id=:depId";
            }
            Object name = param.get("name");
            if (name != null && !"".equals(name)){
                sql += " and c.name like '%"+name+"%'";
            }
            sql += " order by c.sort_id";
            agent.executeNamedSql(sql,param);
            result = agent.resultSetToList();
        }catch (Exception e){
            log.error("查询人员信息异常！！！", e);
        }finally {
            agent.close();
        }
        return result;
    }

    @Override
    public List<Map<String, Object>> getAllDepartmentByAccountId(Map<String, Object> param){
        List<Map<String, Object>> result = new ArrayList<>();
        JDBCAgent agent = new JDBCAgent(false, false);
        try {
            String sql = "select * from org_unit where type='Department' and is_enable='1' and org_account_id=:orgAccountId order by path";
            agent.executeNamedSql(sql, param);
            result = agent.resultSetToList();
        }catch (Exception e){
            log.error("查询单位下部门信息异常！！！", e);
        }finally {
            agent.close();
        }
        return result;
    }

    @Override
    public List<OrgMember> getAllMemberByDepId(Long depId) throws Exception {
        List<OrgMember> result = new ArrayList<>();
        Map<String,Object> param = new HashMap<>();
        param.put("depId", depId);
        String hql = " from "+OrgMember.class.getSimpleName()+" m where m.enable='1' and m.deleted='0' and m.state='1' and m.orgDepartmentId=:depId order by sortId";
        result = DBAgent.find(hql, param);
        return result;
    }

    @Override
    public List<Map<String, Object>> getAllScheduleByNumber(Map<String, Object> param) {
        List<Map<String, Object>> result = new ArrayList<>();
        JDBCAgent agent = new JDBCAgent(false, false);
        try {
            String sql = "select * from " + param.get("tableName") + " where " + param.get("noFie")+"=:no order by start_date desc";
            agent.executeNamedSql(sql, param);
            result = agent.resultSetToList();
            log.info("查询需要更新发起时间的动迁流程表！！！");
        } catch (Exception e) {
            log.error("查询动迁流程表信息异常！！！", e);
        } finally {
            agent.close();
        }
        return result;
    }

    @Override
    public List<Map<String, Object>> getAllSchedule(Map<String, Object> param) {
        List<Map<String,Object>> result = new ArrayList<>();
        JDBCAgent agent = new JDBCAgent(false,false);
        try {
            String sql = "select * from "+param.get("tableName")+" where "+param.get("bhFie")+" is not null and "+param.get("fqsjFie")+" is null and "+param.get("wybsFie")+" is null";
            agent.executeNamedSql(sql,param);
            result = agent.resultSetToList();
            log.info("查询需要更新发起时间的动迁档案！！！");
        }catch (Exception e){
            log.error("查询人员信息异常！！！", e);
        }finally {
            agent.close();
        }
        return result;
    }

    @Override
    public List<OrgMember> getAllMember() throws Exception {
        String hql = "from "+OrgMember.class.getSimpleName()+" m where m.enable='1' and m.deleted='0' and m.state='1' and is_admin='0' ORDER BY createTime";
        List<OrgMember> list = DBAgent.find(hql);
        log.info("查询普通人员信息："+list.size());
        return list;
    }

    @Override
    public List<Map<String, Object>> getAPeopleAllDepIdsByNameAndDate(Map<String, Object> param) {
        List<Map<String,Object>> result = new ArrayList<>();
        JDBCAgent jdbcAgent = new JDBCAgent(false,false);
        try {
            String sql = "select * from "+param.get("tableName")+" where "+param.get("xmFie")+"=:xm and "+
                    param.get("ksrqFie")+"<=:dqDate and "+param.get("jsrqFie")+">=:dqDate";
            log.info("根据人员查询动迁档案："+sql);
            jdbcAgent.executeNamedSql(sql,param);
            result = jdbcAgent.resultSetToList();
            log.info("根据人员查询动迁档案："+result.toString());
        }catch (Exception e){
            log.error("根据人员名称、日期查询动迁档案异常！！！", e);
        }finally {
            jdbcAgent.close();
        }
        return result;
    }

    @Override
    public List<Map<String, Object>> getAPeopleAllDepIds(Long emmberId) {
        List<Map<String,Object>> restult = new ArrayList<>();
        JDBCAgent jdbcAgent = new JDBCAgent(false,false);
        try {
            String sql="select rp.* from org_relationship as rp join org_role as ro on rp.objective1_id=ro.id" +
                    " join org_unit c on  rp.objective0_id = c.id "+
                    " where rp.source_id=:memberId and ro.id in ( " +
                    "select rp2.objective1_id from org_relationship as rp2 where rp2.source_id=:memberId and rp2.type='Member_Role'" +
                    ") and ( ro.name = 'DepLeader' or ro.name = 'DepManager') " +
                    "and ro.type = 1 and ro.bond = 2 and ro.is_enable = 1 and ro.external_type = 0 order by c.path;";
            log.info("查询人员主管部门信息sql："+sql);
            Map<String,Object> param = new HashMap<>();
            param.put("memberId",emmberId);
            jdbcAgent.executeNamedSql(sql,param);
            restult = jdbcAgent.resultSetToList();
        }catch (Exception e){
            log.error("查询人员主管部门信息异常！！！", e);
        }finally {
            jdbcAgent.close();
        }
        return restult;
    }

    @Override
    public List<Map<String,Object>> getRealMembers() {
        List<Map<String,Object>> result = new ArrayList<>();
        JDBCAgent jdbcAgent = new JDBCAgent(false,false);
        try {
            String sql = "select c.id as id from addressbook  a join ctp_enum_item b on a.EXT_ATTR_31 = b.id join org_member c  "
                    +" on a.MEMBER_ID = c.id where b.showvalue = '是' and c.is_enable='1' and c.IS_DELETED='0' and c.STATUS='1' and c.is_admin='0'" +
                    " order by c.sort_id ";
            result = jdbcAgent.findNamedSql(sql);
        }catch (Exception e){
            log.error("查询人员组数据失败", e);
        }finally {
            jdbcAgent.close();
        }
        return result;
    }



    @Override
    public Map<String,Object> getNeedDeptById(Long deptId) {
        List<Map<String,Object>> result = new ArrayList<>();
        JDBCAgent jdbcAgent = new JDBCAgent(false,false);
        try {
            FormApi4Cap4 cap4FormManager = (FormApi4Cap4) AppContext.getBean("formApi4Cap4");
            String deptWeihuCode = AppContext.getSystemProperty("ebsRc.deptWeihuCode");
            FormBean deptFormBean = cap4FormManager.getFormByFormCode(deptWeihuCode);
            FormFieldBean deptNameBean = deptFormBean.getFieldBeanByDisplay("部门名称");
            FormFieldBean deptJcBean = deptFormBean.getFieldBeanByDisplay("部门简称");
            String sql = "select "+deptNameBean.getName()+" as id ,"+deptJcBean.getName()+" as jc from "+deptNameBean.getOwnerTableName()+" where "+deptNameBean.getName()+"=:deptId ";
            Map<String,Object> param = new HashMap<>();
            param.put("deptId",deptId);
            result = jdbcAgent.findNamedSql(sql,param);
        }catch (Exception e){
            log.error("查询人员组数据失败", e);
        }finally {
            jdbcAgent.close();
        }
        if (result!=null&&!result.isEmpty()){
            return result.get(0);
        }else {
            return null;
        }
    }

    @Override
    public List<Map<String, Object>> getAllNeedDept() {
        List<Map<String,Object>> result = new ArrayList<>();
        JDBCAgent jdbcAgent = new JDBCAgent(false,false);
        try {
            FormApi4Cap4 cap4FormManager = (FormApi4Cap4) AppContext.getBean("formApi4Cap4");
            String deptWeihuCode = AppContext.getSystemProperty("ebsRc.deptWeihuCode");
            FormBean deptFormBean = cap4FormManager.getFormByFormCode(deptWeihuCode);
            FormFieldBean deptNameBean = deptFormBean.getFieldBeanByDisplay("部门名称");
            FormFieldBean deptJcBean = deptFormBean.getFieldBeanByDisplay("部门简称");
            FormFieldBean sortBean = deptFormBean.getFieldBeanByDisplay("序号1");
            String sql = "select a."+deptNameBean.getName()+" as id,a."+deptJcBean.getName()+" as jc  from "+deptNameBean.getOwnerTableName()
                    +" a join org_unit b on a."+deptNameBean.getName()+" = b.id  order by a."+sortBean.getName();
            Map<String,Object> param = new HashMap<>();
            result = jdbcAgent.findNamedSql(sql,param);
        }catch (Exception e){
            log.error("查询人员组数据失败", e);
        }finally {
            jdbcAgent.close();
        }
        if (result!=null&&!result.isEmpty()){
            return result;
        }else {
            return null;
        }
    }

    @Override
    public List<Map<String,Object>> getRealDeptMembers(Long deptId) {
        List<Map<String,Object>> result = new ArrayList<>();
        JDBCAgent jdbcAgent = new JDBCAgent(false,false);
        try {
            String sql = "select c.id as id from addressbook  a join ctp_enum_item b on a.EXT_ATTR_31 = b.id join org_member c  "
                    +" on a.MEMBER_ID = c.id where b.showvalue = '是' and c.is_enable='1' and c.IS_DELETED='0' and c.STATUS='1' and c.is_admin='0'"
                    +" and c.ORG_DEPARTMENT_ID = :deptId order by c.sort_id ";
            Map<String, Object> params = new HashMap<>();
            params.put("deptId",deptId);
            result = jdbcAgent.findNamedSql(sql,params);
        }catch (Exception e){
            log.error("查询人员组数据失败", e);
        }finally {
            jdbcAgent.close();
        }
        return result;
    }
}
